<!DOCTYPE html>
<html lang="zh_cn">
<head>
          <title>来玩魔王的咚</title>
        <meta name="viewport" content="width=device-width, initial-scale=1" />
        <meta charset="utf-8" />
        <!-- twitter card metadata -->
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="/images/mowang.png">
<meta name="twitter:site" content="">
<meta name="twitter:title" content="mysql用户权限">
<meta name="twitter:description" content="<p>初始化：mariadb安全加固策略，mysql初始密码设置；查询用户的权限；用户密码设置；数据库权限修改</p>">
        <!-- OG Tags -->
<meta property="og:url" content="/mysql-user-and-grant.html"/>
<meta property="og:title" content="来玩魔王的咚 | mysql用户权限" />
<meta property="og:description" content="<p>初始化：mariadb安全加固策略，mysql初始密码设置；查询用户的权限；用户密码设置；数据库权限修改</p>" />
        <!-- favicon -->
        <link rel="icon" type="image/png" href="/images/mowang.png">
        <!-- moment.js for date formatting -->
        <script src="/theme/js/moment.js"></script>
        <!-- css -->
        <link rel="stylesheet" type="text/css" href="/theme/css/main.css" />
        <!-- 左边的menu，如果页面高度不够，就跟着滚动，否则文章分类显示不全 -->
        <link rel="stylesheet" type="text/css" href="/theme/css/mycss/menu.css" />
		<script>
			
                /*! grunt-grunticon Stylesheet Loader - v2.1.2 | https://github.com/filamentgroup/grunticon | (c) 2015 Scott Jehl, Filament Group, Inc. | MIT license. */
    
    (function(e){function t(t,n,r,o){"use strict";function a(){for(var e,n=0;u.length>n;n++)u[n].href&&u[n].href.indexOf(t)>-1&&(e=!0);e?i.media=r||"all":setTimeout(a)}var i=e.document.createElement("link"),l=n||e.document.getElementsByTagName("script")[0],u=e.document.styleSheets;return i.rel="stylesheet",i.href=t,i.media="only x",i.onload=o||null,l.parentNode.insertBefore(i,l),a(),i}var n=function(r,o){"use strict";if(r&&3===r.length){var a=e.navigator,i=e.Image,l=!(!document.createElementNS||!document.createElementNS("http://www.w3.org/2000/svg","svg").createSVGRect||!document.implementation.hasFeature("http://www.w3.org/TR/SVG11/feature#Image","1.1")||e.opera&&-1===a.userAgent.indexOf("Chrome")||-1!==a.userAgent.indexOf("Series40")),u=new i;u.onerror=function(){n.method="png",n.href=r[2],t(r[2])},u.onload=function(){var e=1===u.width&&1===u.height,a=r[e&&l?0:e?1:2];n.method=e&&l?"svg":e?"datapng":"png",n.href=a,t(a,null,null,o)},u.src="",document.documentElement.className+=" grunticon"}};n.loadCSS=t,e.grunticon=n})(this);(function(e,t){"use strict";var n=t.document,r="grunticon:",o=function(e){if(n.attachEvent?"complete"===n.readyState:"loading"!==n.readyState)e();else{var t=!1;n.addEventListener("readystatechange",function(){t||(t=!0,e())},!1)}},a=function(e){return t.document.querySelector('link[href$="'+e+'"]')},c=function(e){var t,n,o,a,c,i,u={};if(t=e.sheet,!t)return u;n=t.cssRules?t.cssRules:t.rules;for(var l=0;n.length>l;l++)o=n[l].cssText,a=r+n[l].selectorText,c=o.split(");")[0].match(/US\-ASCII\,([^"']+)/),c&&c[1]&&(i=decodeURIComponent(c[1]),u[a]=i);return u},i=function(e){var t,o,a;o="data-grunticon-embed";for(var c in e)if(a=c.slice(r.length),t=n.querySelectorAll(a+"["+o+"]"),t.length)for(var i=0;t.length>i;i++)t[i].innerHTML=e[c],t[i].style.backgroundImage="none",t[i].removeAttribute(o);return t},u=function(t){"svg"===e.method&&o(function(){i(c(a(e.href))),"function"==typeof t&&t()})};e.embedIcons=i,e.getCSS=a,e.getIcons=c,e.ready=o,e.svgLoadedCallback=u,e.embedSVG=u})(grunticon,this);
                
                grunticon(["/theme/css/icons.data.svg.css", "/theme/css/icons.data.png.css", "/theme/css/icons.fallback.css"]);
            </script>
        <noscript><link href="/theme/css/icons.fallback.css" rel="stylesheet"></noscript>
        <!-- menu toggle javascript -->
        <script type="text/javascript">
            document.addEventListener("DOMContentLoaded", initMenu);
            
            function initMenu(){
                var menu = document.getElementById("menu");
                var menulink = document.getElementById("menu-link");
                menulink.addEventListener("click", function toggleMenu(){
                        window.event.preventDefault();
                        menulink.classList.toggle('active');
                        menu.classList.toggle('active');              
                    });
            };
        </script>
        <!-- 不蒜子 -->
        <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

    <meta name="description" content="<p>初始化：mariadb安全加固策略，mysql初始密码设置；查询用户的权限；用户密码设置；数据库权限修改</p>" />

    <meta name="tags" content="mysql" />
  <!-- 替换部分base的样式，看文章时，要再宽一点，右边有很多空间可以撑开 -->
  <link rel="stylesheet" type="text/css" href="/theme/css/mycss/article.css" />

</head>
<body>
    <div role="banner" id="masthead">
        <header>
            <a href="/"><img src="/images/mowang.png" alt="McManus Logo"></a>
                <h1>来玩魔王的咚@骑士救兵</h1>
            <a href="#menu" id="menu-link">more stuff</a>
            <nav id="menu">
                <ul>
                        <li><a href="/tags">tags</a></li>
                            <li><a href="/category/cloud.html">Cloud</a></li>
                            <li><a href="/category/docker.html">Docker</a></li>
                            <li><a href="/category/go.html">Go</a></li>
                            <li class="active"><a href="/category/linux.html">Linux</a></li>
                            <li><a href="/category/python.html">Python</a></li>
                            <li><a href="/category/xue-xi-bi-ji.html">学习笔记</a></li>
                            <li><a href="/category/yun-wei-zi-dong-hua.html">运维自动化</a></li>
                </ul>
            </nav>
        </header>
    </div>
        <div class="page" role="main">
  <div class="article" role="article">
    <article>
        <footer>
            <a name="top"></a>
            <p>
              <time datetime=" 2020-08-19 12:40:00+08:00">
                <script>document.write(moment('2020-08-19 12:40:00+08:00').format('LL'));</script>
              </time>
              ~
              <time datetime=" 2020-08-19 12:40:00+08:00">
                <script>document.write(moment('2020-08-19 15:00:00+08:00').format('LL'));</script>
              </time>
            </p>
        </footer>
        <header>
          <h2>
            mysql用户权限
          </h2>
        </header>
      <div class="content">
         <div class="toc">
<ul>
<li><a href="#chu-shi-hua">初始化</a><ul>
<li><a href="#mariadb-an-quan-jia-gu-ce-lve">mariadb 安全加固策略</a></li>
<li><a href="#mysql-chu-shi-mi-ma-she-zhi">mysql 初始密码设置</a></li>
<li><a href="#ren-zheng-wen-jian">认证文件</a></li>
</ul>
</li>
<li><a href="#cha-xun-yong-hu-de-quan-xian">查询用户的权限</a></li>
<li><a href="#yong-hu-he-mi-ma">用户和密码</a><ul>
<li><a href="#chuang-jian-yong-hu">创建用户</a></li>
<li><a href="#yong-hu-gai-ming-shan-chu">用户改名/删除</a></li>
<li><a href="#xiu-gai-mi-ma">修改密码</a></li>
</ul>
</li>
<li><a href="#quan-xian">权限</a><ul>
<li><a href="#grant-shou-yu-quan-xian">GRANT 授予权限</a></li>
<li><a href="#revoke-hui-shou-quan-xian">REVOKE 回收权限</a></li>
</ul>
</li>
</ul>
</div>
<h3 id="chu-shi-hua"><a class="toclink" href="#chu-shi-hua">初始化</a></h3>
<p>安装就略过了。安装完mysql或者mariadb之后，在正式使用之前需要先做一些工作。<br>
首先，验证一下安装是否成功：</p>
<div class="highlight"><pre><span></span><code>$ mysqladmin --version
mysqladmin  Ver <span class="m">9</span>.0 Distrib <span class="m">5</span>.5.64-MariaDB, <span class="k">for</span> Linux on x86_64
$
$ mysqladmin --version
mysqladmin  Ver <span class="m">8</span>.42 Distrib <span class="m">5</span>.7.31, <span class="k">for</span> Linux on x86_64
</code></pre></div>

<p>上面的命令可以查看mysql的版本。  </p>
<p>另外，启动mysql也是必须的。否则无法登录：</p>
<div class="highlight"><pre><span></span><code>$ systemctl start mariadb/mysql
$ systemctl <span class="nb">enable</span> mariadb/mysql
</code></pre></div>

<p>准备工作完成了，之后的操作mysql和mariadb有区别，分开讲。  </p>
<h4 id="mariadb-an-quan-jia-gu-ce-lve"><a class="toclink" href="#mariadb-an-quan-jia-gu-ce-lve">mariadb 安全加固策略</a></h4>
<p>在mariadb上，不是必须的，不过最好还是做一下。<br>
只要执行一条命令，交互式的回答几条问题就好了：</p>
<div class="highlight"><pre><span></span><code>$ mysql_secure_installation
</code></pre></div>

<p>命令进入后，交互的选项如下：</p>
<ol>
<li>输入密码。初始root为空，直接回车就好。</li>
<li>是否设置root密码？当然要加密码，选y</li>
<li>设置root密码</li>
<li>再次输入root密码</li>
<li>是否删除匿名用户。没用，删掉</li>
<li>是否禁止root远程登录。最好还是禁掉，创建其他用户来远程登录</li>
<li>是否删除test库。没用，删掉</li>
<li>是否重新加载权限表。稳一点，要的</li>
</ol>
<p>完成。  </p>
<p><strong>自动化安装的场景</strong><br>
由于命令是交互式的，不适合自动化操作。并且执行mysql_secure_installation也不是必须的。所以就不执行这个命令了。<br>
但是安全设置是不能缺少的，参考上面的设置只能自己用脚本实现了。<br>
其实mysql_secure_installation本身就是一个shell脚本，把交互的部分去掉，直接把命令提出来写个脚本也可行：</p>
<div class="highlight"><pre><span></span><code>$ rpm -ql mariadb-server <span class="p">|</span> grep /usr/bin/mysql_secure_installation
/usr/bin/mysql_secure_installation
</code></pre></div>

<h4 id="mysql-chu-shi-mi-ma-she-zhi"><a class="toclink" href="#mysql-chu-shi-mi-ma-she-zhi">mysql 初始密码设置</a></h4>
<p>mysql，默认已经做好了安全策略，但是在密码上会遇到一些问题。<br>
默认禁止使用简单密码，并且系统的初始密码也是随机创建的，如果找不到连mysql都进不去。  </p>
<p>初始密码在日志文件中，查找初始密码：</p>
<div class="highlight"><pre><span></span><code>$ grep <span class="s1">&#39;temporary password&#39;</span> /var/log/mysqld.log
</code></pre></div>

<p>初始的随机密码当然要改掉。默认的安全策略要求，设置的密码必须足够复杂，不过也是可以将默认设置改掉的。<br>
首先要进入系统</p>
<div class="highlight"><pre><span></span><code><span class="c1"># 先设置一个复杂密码</span><span class="w"></span>
<span class="o">&gt;</span><span class="w"> </span><span class="k">ALTER</span><span class="w"> </span><span class="k">USER</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="w"> </span><span class="k">IDENTIFIED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;123456!Qa&#39;</span><span class="p">;</span><span class="w"></span>
<span class="c1"># 查看密码策略设置</span><span class="w"></span>
<span class="o">&gt;</span><span class="w"> </span><span class="k">show</span><span class="w"> </span><span class="k">variables</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;%validate_password%&#39;</span><span class="p">;</span><span class="w"></span>
<span class="c1"># 允许简单密码</span><span class="w"></span>
<span class="o">&gt;</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="w"> </span><span class="n">validate_password_policy</span><span class="o">=</span><span class="mi">0</span><span class="p">;</span><span class="w"></span>
<span class="c1"># 设置最小密码长度</span><span class="w"></span>
<span class="o">&gt;</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="w"> </span><span class="n">validate_password_length</span><span class="o">=</span><span class="mi">6</span><span class="p">;</span><span class="w"></span>
<span class="c1"># 再次设置密码，可以用简单的了</span><span class="w"></span>
<span class="o">&gt;</span><span class="w"> </span><span class="k">ALTER</span><span class="w"> </span><span class="k">USER</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="w"> </span><span class="k">IDENTIFIED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="p">;</span><span class="w"></span>
</code></pre></div>

<h4 id="ren-zheng-wen-jian"><a class="toclink" href="#ren-zheng-wen-jian">认证文件</a></h4>
<p>在用户家目录下创建指定的文件，写上用户名和密码，之后可以用mysql命令直接以指定的用户名和密码登录。<br>
并且这个设置在执行脚本的时候，可以避免在命令行中输出明文的密码。<br>
文件很简单，具体如下：</p>
<div class="highlight"><pre><span></span><code>$ cat .my.cnf 
<span class="o">[</span>client<span class="o">]</span>
<span class="nv">user</span> <span class="o">=</span> root
<span class="nv">password</span> <span class="o">=</span> <span class="m">123456</span>
</code></pre></div>

<h3 id="cha-xun-yong-hu-de-quan-xian"><a class="toclink" href="#cha-xun-yong-hu-de-quan-xian">查询用户的权限</a></h3>
<p>查询一共用哪些用户：</p>
<div class="highlight"><pre><span></span><code><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="k">DISTINCT</span><span class="w"> </span><span class="nf">CONCAT</span><span class="p">(</span><span class="s1">&#39;User: </span><span class="se">&#39;&#39;</span><span class="s1">&#39;</span><span class="p">,</span><span class="k">user</span><span class="p">,</span><span class="s1">&#39;</span><span class="se">&#39;&#39;</span><span class="s1">@</span><span class="se">&#39;&#39;</span><span class="s1">&#39;</span><span class="p">,</span><span class="k">host</span><span class="p">,</span><span class="s1">&#39;</span><span class="se">&#39;&#39;</span><span class="s1">;&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="k">query</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="k">user</span><span class="p">;</span><span class="w"></span>
<span class="o">+------------------------------------+</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">query</span><span class="w">                              </span><span class="o">|</span><span class="w"></span>
<span class="o">+------------------------------------+</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">User</span><span class="o">:</span><span class="w"> </span><span class="s1">&#39;steed&#39;</span><span class="nv">@&#39;192.168.1.%&#39;</span><span class="p">;</span><span class="w">       </span><span class="o">|</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">User</span><span class="o">:</span><span class="w"> </span><span class="s1">&#39;mysql.session&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="p">;</span><span class="w"> </span><span class="o">|</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">User</span><span class="o">:</span><span class="w"> </span><span class="s1">&#39;mysql.sys&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="p">;</span><span class="w">     </span><span class="o">|</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">User</span><span class="o">:</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="p">;</span><span class="w">          </span><span class="o">|</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">User</span><span class="o">:</span><span class="w"> </span><span class="s1">&#39;steed&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="p">;</span><span class="w">         </span><span class="o">|</span><span class="w"></span>
<span class="o">+------------------------------------+</span><span class="w"></span>
<span class="mi">6</span><span class="w"> </span><span class="k">rows</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="kt">set</span><span class="w"> </span><span class="p">(</span><span class="mf">0.01</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w"></span>
</code></pre></div>

<p>查询用户权限，缺省FOR是查询自己:</p>
<div class="highlight"><pre><span></span><code><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">SHOW</span><span class="w"> </span><span class="k">GRANTS</span><span class="p">;</span><span class="w"></span>
<span class="o">+---------------------------------------------------------------------+</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">Grants</span><span class="w"> </span><span class="k">for</span><span class="w"> </span><span class="n">root</span><span class="nv">@localhost</span><span class="w">                                           </span><span class="o">|</span><span class="w"></span>
<span class="o">+---------------------------------------------------------------------+</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">GRANT</span><span class="w"> </span><span class="k">ALL</span><span class="w"> </span><span class="k">PRIVILEGES</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="o">*</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="w"> </span><span class="k">WITH</span><span class="w"> </span><span class="k">GRANT</span><span class="w"> </span><span class="k">OPTION</span><span class="w"> </span><span class="o">|</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">GRANT</span><span class="w"> </span><span class="k">PROXY</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="s1">&#39;&#39;</span><span class="nv">@&#39;&#39;</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="w"> </span><span class="k">WITH</span><span class="w"> </span><span class="k">GRANT</span><span class="w"> </span><span class="k">OPTION</span><span class="w">        </span><span class="o">|</span><span class="w"></span>
<span class="o">+---------------------------------------------------------------------+</span><span class="w"></span>
<span class="mi">2</span><span class="w"> </span><span class="k">rows</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="kt">set</span><span class="w"> </span><span class="p">(</span><span class="mf">0.00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w"></span>

<span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">SHOW</span><span class="w"> </span><span class="k">GRANTS</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="s1">&#39;zabbix&#39;</span><span class="nv">@&#39;%&#39;</span><span class="p">;</span><span class="w">      </span>
<span class="o">+----------------------------------------------------+</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">Grants</span><span class="w"> </span><span class="k">for</span><span class="w"> </span><span class="n">zabbix</span><span class="err">@</span><span class="o">%</span><span class="w">                                </span><span class="o">|</span><span class="w"></span>
<span class="o">+----------------------------------------------------+</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">GRANT</span><span class="w"> </span><span class="k">USAGE</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="o">*</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="s1">&#39;zabbix&#39;</span><span class="nv">@&#39;%&#39;</span><span class="w">                 </span><span class="o">|</span><span class="w"></span>
<span class="o">|</span><span class="w"> </span><span class="k">GRANT</span><span class="w"> </span><span class="k">ALL</span><span class="w"> </span><span class="k">PRIVILEGES</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n n-Quoted">`zabbix`</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="s1">&#39;zabbix&#39;</span><span class="nv">@&#39;%&#39;</span><span class="w"> </span><span class="o">|</span><span class="w"></span>
<span class="o">+----------------------------------------------------+</span><span class="w"></span>
<span class="mi">2</span><span class="w"> </span><span class="k">rows</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="kt">set</span><span class="w"> </span><span class="p">(</span><span class="mf">0.00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w"></span>
</code></pre></div>

<p>权限后面有<code>WITH GRANT OPTION</code>的，表示允许将自己的权限授权给别的用户。<br>
这里的库名加了反引号，后面的用户名和主机加的是单引号。自己写命令的时候要注意。  </p>
<h3 id="yong-hu-he-mi-ma"><a class="toclink" href="#yong-hu-he-mi-ma">用户和密码</a></h3>
<h4 id="chuang-jian-yong-hu"><a class="toclink" href="#chuang-jian-yong-hu">创建用户</a></h4>
<p>用GRANT命令来创建新用户。这个命令是用来授权数据库操作权限的，将权限授权给指定的用户，如果用户不存在则创建。<br>
所以可以用来创建用户，命令格式如下：</p>
<div class="highlight"><pre><span></span><code>GRANT SELECT,UPDATE,DELETE,INSERT,CREATE,DROP ON 库名.表名 TO 用户名@主机 IDENTIFIED BY &#39;密码&#39; ;
GRANT ALL PRIVILEGES ON *.* TO &#39;用户名&#39;@&#39;%&#39; IDENTIFIED BY &#39;密码&#39; WITH GRANT OPTION;
</code></pre></div>

<p>上面各个关键字的说明：</p>
<ul>
<li>ALL PRIVILEGES: 将所有的权限授予用户。也可以指定授予哪些权限，比如：SELECT,UPDATE等。</li>
<li>ON: 指定对具体哪个库，哪个表生效。<code>mysql.*</code>表示某个库的所有表，<code>*.*</code>表示所有库的所有表。</li>
<li>TO: 将权限授予哪个用户。<code>'用户名'@'%'</code>合起来才表示一个用户。%表示任意主机，<code>192.168.1.%</code>可以表示一个网段。</li>
<li>IDENTIFIED BY: 设置密码。如果是已有的用户，可以不加。也可以加上，会覆盖掉旧密码。</li>
<li>WITH GRANT OPTION: 允许用户将自己的权限授权给其它用户。一般都不加，授权的事情交给本地root账号操作就好。</li>
</ul>
<p>关于引号的使用，可以参考<code>SHOW GRANTS</code>的输出，引号不是必须的。这里还是说明一下：</p>
<ul>
<li>库名：用反引号</li>
<li>表名：应该也是反引号。一般都是*，星号不能加引号</li>
<li>用户名：用单引号</li>
<li>主机：用单引号。如果要用%，则必须用引号引起来</li>
<li>密码：用单引号，并且必须用引号引起来</li>
</ul>
<h4 id="yong-hu-gai-ming-shan-chu"><a class="toclink" href="#yong-hu-gai-ming-shan-chu">用户改名/删除</a></h4>
<p>用户名@主机，才是一个完整的用户名，使用同样的密码。<br>
仅仅用户名一样，不同的主机，使用的是不同的密码。<br>
为了避免混淆，同一用户名不同的主机，设置相同的密码。有办法可以批量改相同用户名的密码。  </p>
<p><strong>用户改名</strong><br>
用户不仅包含登录使用的用户名，还有登录主机的信息。所以有时候需要修改一下，改变登录主机的范围。  </p>
<div class="highlight"><pre><span></span><code><span class="k">RENAME</span><span class="w"> </span><span class="k">USER</span><span class="w"> </span><span class="s1">&#39;steed&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="s1">&#39;steed&#39;</span><span class="nv">@&#39;%&#39;</span><span class="p">;</span><span class="w"></span>
</code></pre></div>

<p>不能修改为已存在的用户名。  </p>
<p><strong>删除用户</strong>  </p>
<div class="highlight"><pre><span></span><code><span class="k">DROP</span><span class="w"> </span><span class="k">USER</span><span class="w"> </span><span class="s1">&#39;steed&#39;</span><span class="nv">@&#39;%&#39;</span><span class="p">;</span><span class="w"></span>
</code></pre></div>

<h4 id="xiu-gai-mi-ma"><a class="toclink" href="#xiu-gai-mi-ma">修改密码</a></h4>
<p>修改密码的方法有很多。  </p>
<p><strong>GRANT命令</strong><br>
GRANT命令主要是授权，不过后面的IDENTIFIED BY可以修改密码。另外可重复授予已有的权限，权限上不会有任何变化。  </p>
<p><strong>ALTER USER</strong><br>
这个应该是推荐使用的命令：  </p>
<div class="highlight"><pre><span></span><code><span class="k">ALTER</span><span class="w"> </span><span class="k">USER</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="w"> </span><span class="k">IDENTIFIED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;密码&#39;</span><span class="p">;</span><span class="w"></span>
</code></pre></div>

<p><strong>SET PASSWORD</strong>  </p>
<div class="highlight"><pre><span></span><code><span class="k">SET</span><span class="w"> </span><span class="k">PASSWORD</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="nv">@&#39;localhost&#39;</span><span class="o">=</span><span class="k">PASSWORD</span><span class="p">(</span><span class="s1">&#39;密码&#39;</span><span class="p">);</span><span class="w"></span>
</code></pre></div>

<p><strong>mysqladmin</strong><br>
这个是在外面的shell中完成修改：</p>
<div class="highlight"><pre><span></span><code>$ mysqladmin -usteed -p password <span class="o">[</span>new-password<span class="o">]</span>
Enter password: 
</code></pre></div>

<p>输入密码验证正确后，新密码就生效了。  </p>
<p><strong>UPDATE 批量改密码</strong><br>
密码也是保存在表里的，所以可以更新数据表的记录实现修改密码：</p>
<div class="highlight"><pre><span></span><code><span class="c1"># mysql 的字段名是 authentication_string</span><span class="w"></span>
<span class="k">UPDATE</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="k">user</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">authentication_string</span><span class="o">=</span><span class="k">PASSWORD</span><span class="p">(</span><span class="s1">&#39;密码&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="k">user</span><span class="o">=</span><span class="s1">&#39;steed&#39;</span><span class="p">;</span><span class="w"></span>
<span class="c1"># mariadb 的字段名是 Password</span><span class="w"></span>
<span class="k">UPDATE</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="k">user</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">Password</span><span class="o">=</span><span class="k">PASSWORD</span><span class="p">(</span><span class="s1">&#39;$esc_pass&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="k">User</span><span class="o">=</span><span class="s1">&#39;root&#39;</span><span class="p">;</span><span class="w"></span>
<span class="c1"># 必须重载才能生效</span><span class="w"></span>
<span class="k">FLUSH</span><span class="w"> </span><span class="k">PRIVILEGES</span><span class="p">;</span><span class="w"></span>
</code></pre></div>

<p>mysql_secure_installation里也是用这个来修改root密码的。<br>
因为是修改记录，所以不会立即生效，需要执行一下<code>FLUSH PRIVILEGES</code>。<br>
mysql和mariadb使用的字段名不同，这个要注意。  </p>
<h3 id="quan-xian"><a class="toclink" href="#quan-xian">权限</a></h3>
<p>这里的权限指的是数据库的增、删、改、查等权限。登录的权限（用户名、密码、允许的主机）上面已经讲过了。  </p>
<h4 id="grant-shou-yu-quan-xian"><a class="toclink" href="#grant-shou-yu-quan-xian">GRANT 授予权限</a></h4>
<p>GRANT命令上面已经讲过了，使用该命令创建用户，设置密码，并授予一定的权限。之后可以继续使用GRANT命令追加授予更多的权限。<br>
可以多次重复执行该命令。原本用户没有的权限，会追加给用户。用户已有的权限，也不会有问题。<br>
对于已经存在的用户，<code>IDENTIFIED BY</code>可以省了。加上也行，就是改密码。<br>
为用户追加权限：</p>
<div class="highlight"><pre><span></span><code><span class="k">GRANT</span><span class="w"> </span><span class="k">CREATE</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="n">steed</span><span class="nv">@localhost</span><span class="p">;</span><span class="w"></span>
</code></pre></div>

<h4 id="revoke-hui-shou-quan-xian"><a class="toclink" href="#revoke-hui-shou-quan-xian">REVOKE 回收权限</a></h4>
<p>GRANT 命令不能收回用户已有的权限，要回收权限，可以用 REVOKE 命令：</p>
<div class="highlight"><pre><span></span><code><span class="k">REVOKE</span><span class="w"> </span><span class="k">CREATE</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">steed</span><span class="nv">@localhost</span><span class="p">;</span><span class="w"></span>
</code></pre></div>

<p>REVOKE和GRANT差不多。特别注意，要把<strong>TO</strong>改成<strong>FROM</strong>。  </p>
      </div>
      <div class="back-to-top">
        <a href="/">HOME</a>
        <a href="#top">TOP</a>
      </div>
    </article>
  </div>
<!-- end article -->
<!-- 页面往下滚动一段之后才会显示TOC -->
<script>
  window.onscroll = function() {
    var tocbox = document.getElementsByClassName('toc')[0];
    var osTop = document.documentElement.scrollTop || document.body.scrollTop;
    var osWidth = document.documentElement.scrollWidth || document.body.scrollWidth;
    // console.log(osTop)
    if (osTop>300 && osWidth>865) {
      tocbox.style.display = "block"
    }
    if (osTop<300 || osWidth<865) {
      tocbox.style.display = "none"
    }
  }
</script>
                <footer>
                    <div class="icons">
                    </div>
                    <span id="busuanzi_container_page_pv" style="padding: 10px">本文阅读量<span id="busuanzi_value_page_pv"></span>次</span>
                    <span id="busuanzi_container_site_pv" style="padding: 10px">本站总访问量<span id="busuanzi_value_site_pv"></span>次</span>
                    <span id="busuanzi_container_site_uv" style="padding: 10px">本站总访客数<span id="busuanzi_value_site_uv"></span>人</span>
                    <p>© <script>document.write(moment().format('YYYY'));</script> 749B</p>
                </footer>
        </div>
</body>
</html>